﻿Imports System.Data
Imports System.Data.SqlClient
Imports BE = Entidades
Imports LogicDAO

Public Class ClienteDAO
    Dim conexion As SqlConnection
    Dim cn As New Conexion
    Dim comando As SqlDataAdapter
    Public Shared errores As String
    Dim cmd As SqlCommand

    Public Function ListarClientes() As DataSet
        Dim ds As New DataSet
        Try

            conexion = cn.Conectar
            comando = New SqlDataAdapter("usp_ListaClientes", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Llenamos el dataset
            comando.Fill(ds, "Clientes")

        Catch ex As Exception
            errores = ex.Message
        Finally
            'Destruimos los objetos
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try
        Return ds

    End Function
    Public Function ActualizarEstadoCliente(ByVal objCliBE As BE.Cliente) As String
        Dim cn As New Conexion
        Dim msg As String = ""
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("usp_actualizar_Estado_cliente", conexion)
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Char, 6))
            cmd.Parameters.Add(New SqlParameter("@estado", SqlDbType.VarChar, 11))

            cmd.Parameters("@codigo").Direction = ParameterDirection.Input
            cmd.Parameters("@estado").Direction = ParameterDirection.Input

            cmd.Parameters("@codigo").Value = objCliBE.Codigo
            cmd.Parameters("@estado").Value = objCliBE.Estado


            conexion.Open()
            cmd.ExecuteNonQuery()
            msg = "Estado Actualizado!!"

        Catch ex As Exception
            msg = "Error al actualizar!!"
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try
        Return msg
    End Function
    Public Function RegistrarCliente(ByVal objCliBE As BE.Cliente) As String
        Dim cn As New Conexion
        Dim msg As String = ""
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("usp_RegistraCliente", conexion)
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@DNI", SqlDbType.Char, 8))
            cmd.Parameters.Add(New SqlParameter("@NOMBRES", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@APELLIDOS", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@DIRECCION", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@TELEFONO", SqlDbType.VarChar, 9))
            cmd.Parameters.Add(New SqlParameter("@ESTADO", SqlDbType.VarChar, 11))
            cmd.Parameters.Add(New SqlParameter("@EMAIL", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@PASSWORD", SqlDbType.VarChar, 10))
            cmd.Parameters.Add(New SqlParameter("@DISTRITO", SqlDbType.VarChar, 6))

            cmd.Parameters("@DNI").Direction = ParameterDirection.Input
            cmd.Parameters("@NOMBRES").Direction = ParameterDirection.Input
            cmd.Parameters("@APELLIDOS").Direction = ParameterDirection.Input
            cmd.Parameters("@DIRECCION").Direction = ParameterDirection.Input
            cmd.Parameters("@TELEFONO").Direction = ParameterDirection.Input
            cmd.Parameters("@ESTADO").Direction = ParameterDirection.Input
            cmd.Parameters("@EMAIL").Direction = ParameterDirection.Input
            cmd.Parameters("@PASSWORD").Direction = ParameterDirection.Input
            cmd.Parameters("@DISTRITO").Direction = ParameterDirection.Input

            cmd.Parameters("@DNI").Value = objCliBE.Dni
            cmd.Parameters("@NOMBRES").Value = objCliBE.Nombre
            cmd.Parameters("@APELLIDOS").Value = objCliBE.Apellido
            cmd.Parameters("@DIRECCION").Value = objCliBE.Direccion
            cmd.Parameters("@TELEFONO").Value = objCliBE.Telefono
            cmd.Parameters("@ESTADO").Value = objCliBE.Estado
            cmd.Parameters("@PASSWORD").Value = objCliBE.Password
            cmd.Parameters("@EMAIL").Value = objCliBE.Email
            cmd.Parameters("@DISTRITO").Value = objCliBE.Distrito


            conexion.Open()
            cmd.ExecuteNonQuery()
            msg = "Registro Exitoso .... Puede Ingresar ...!!"

        Catch ex As Exception
            msg = "Error al ingresar!!"
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try
        Return msg

    End Function

    Public Function Actualizar(ByVal objCliBE As BE.Cliente) As String

        Dim cn As New Conexion
        Dim msg As String = ""
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("usp_ActualizaCliente", conexion)
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Char, 6))
            cmd.Parameters.Add(New SqlParameter("@dni", SqlDbType.Char, 8))
            cmd.Parameters.Add(New SqlParameter("@nombres", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@apellidos", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@direccion", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@telefono", SqlDbType.VarChar, 9))
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar, 30))
            cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.VarChar, 10))
            cmd.Parameters.Add(New SqlParameter("@distrito", SqlDbType.VarChar, 6))


            cmd.Parameters("@codigo").Direction = ParameterDirection.Input
            cmd.Parameters("@dni").Direction = ParameterDirection.Input
            cmd.Parameters("@nombres").Direction = ParameterDirection.Input
            cmd.Parameters("@apellidos").Direction = ParameterDirection.Input
            cmd.Parameters("@direccion").Direction = ParameterDirection.Input
            cmd.Parameters("@telefono").Direction = ParameterDirection.Input
            cmd.Parameters("@email").Direction = ParameterDirection.Input
            cmd.Parameters("@password").Direction = ParameterDirection.Input
            cmd.Parameters("@distrito").Direction = ParameterDirection.Input


            cmd.Parameters("@codigo").Value = objCliBE.Codigo
            cmd.Parameters("@dni").Value = objCliBE.Dni
            cmd.Parameters("@nombres").Value = objCliBE.Nombre
            cmd.Parameters("@apellidos").Value = objCliBE.Apellido
            cmd.Parameters("@direccion").Value = objCliBE.Direccion
            cmd.Parameters("@telefono").Value = objCliBE.Telefono
            cmd.Parameters("@email").Value = objCliBE.Email
            cmd.Parameters("@password").Value = objCliBE.Password
            cmd.Parameters("@distrito").Value = objCliBE.Distrito

            conexion.Open()
            cmd.ExecuteNonQuery()
            msg = "Actualiza Cliente !!"
            conexion.Close()

        Catch ex As Exception
            msg = "Error !!"
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try
        Return msg




    End Function



    Public Function getCliente(ByVal dni As String, ByVal pass As String) As DataTable

        conexion = cn.Conectar

        Dim da As New SqlDataAdapter("select * from tb_cliente " _
                                     + " where dni_cli='" + dni + "' and pass_cli='" + pass + "'", conexion)
        Dim dt As New DataTable
        da.Fill(dt)
        Return dt
    End Function
    Public Function getClienteXCodigo(ByVal cod As String) As DataTable
        conexion = cn.Conectar


        Dim da As New SqlDataAdapter("select * from tb_cliente where cod_cli='" + cod + "'", conexion)
        Dim dt As New DataTable
        da.Fill(dt)
        Return dt



    End Function


    

End Class
